# -*- coding: utf-8 -*-
import sqlite3
conn=sqlite3.connect(r'cd.db3')
conn.text_factory = str

crm=conn.execute('select id,company,boxs,cds from (select cd_companyid,sum(cd_box) as boxs,count(cd_companyid) as cds from cd group by cd_companyid) left join company on id=cd_companyid').fetchall()
for r in crm:
	# print r[0],r[1],r[2],r[3]
	id=str(r[0])
	company=str(r[1])
	if(r[2]==None):
		boxs="0"
		print id,boxs
	else:
		boxs=str(r[2])
	cds=str(r[3])
	conn.execute("update crm set company='"+company+"',boxs="+boxs+",cds="+cds+" where id='"+id+"'")
conn.commit()
conn.close()
